/*
	EXEC sp_pending_shipment_report '0','0','0',-1,14,'XLREPORT','0','0'
	EXEC sp_pending_shipment_report '0','0','0',0,14,'XLREPORT','0','0'
*/
ALTER PROC sp_pending_shipment_report

@CUSTOMER_ID 	VARCHAR(200),
@PRODUCT_ID	VARCHAR(200),
@SO_NUMBER	VARCHAR(50),
@ORDER_TYPE	INT,
@DATERANGE	INT,
@REPORT_TYPE 	VARCHAR(30),
@VENDOR		VARCHAR(50),
@DEFAULT_PROMPT	VARCHAR(50)

AS
BEGIN
	CREATE TABLE #PENDING_SHIPMENT_TABLE
			([LINE ITEM NO] INT,[COOL QTY] INT, [REQUESTED DATE] DATETIME, [REQUESTED DATE WORK WEEK NO] INT,
			 [REQUESTED DATE QTR] INT,[SCHEDULE DATE] DATETIME, [SCHEDULE DATE WORK WEEK NO] INT, [SCHEDULE DATE QUARTER] INT,
			 [SALES ORDER NUMBER] VARCHAR(50), [CUSTOMER PO NUMBER] VARCHAR(50), [SHIPPING METHOD] VARCHAR(50),
			 [SHIPPING ACCOUNT] VARCHAR(50), [PART NUMBER] VARCHAR(50), CUSTOMER VARCHAR(50),VENDOR VARCHAR(50), Comments VARCHAR(500), [Shipping Instructions] VARCHAR(500), [PACKING SLIP NO] VARCHAR(50) )

	DECLARE @SQLSTRING 		NVARCHAR(4000)
	DECLARE @PARAMDEFINITION 	NVARCHAR(500)
	DECLARE @START_DATE		DATETIME
	DECLARE	@END_DATE		DATETIME
	DECLARE @DEFAULT_VENDOR		INT

	SELECT @DEFAULT_VENDOR = LOCATION_ID FROM LOCATION WHERE VENDOR_ID = (SELECT DEFAULT_SHIPPING_LOCTION_ID FROM CLIENT_TABLE) AND L_TYPE = 'Finished Goods'  

	SET @PARAMDEFINITION = N'@PRM_CUSTOMER_ID VARCHAR(200),@PRM_PRODUCT_ID VARCHAR(200),@PRM_SO_NUMBER VARCHAR(50),@PRM_ORDER_TYPE INT,@PRM_START_DATE DATETIME,@PRM_END_DATE DATETIME,@PRM_VENDOR VARCHAR(50),@PRM_DEFAULT_VENDOR INT'

	SET @SQLSTRING = N'INSERT INTO #PENDING_SHIPMENT_TABLE
	SELECT 
		COOL_LINE_ITEM_NO,(COOL_QTY-ISNULL(XYZ.TOTAL,0)) AS COOL_QTY,
		COOL_REQUESTED_DATE,COOL.REQUESTED_DATE_WW_NO,REQUESTED_DATE_QTR_NO,
		ISNULL(COOL_REVISED_DATE,COOL_SCHEDULE_DATE) AS COOL_SCHEDULE_DATE,
		ISNULL(REVISED_DATE_WW_NO,SCHEDULE_DATE_WW_NO) AS SCHEDULE_DATE_WW_NO,
		ISNULL(REVISED_DATE_QTR_NO,SCHEDULE_DATE_QTR_NO) AS SCHEDULE_DATE_QTR_NO,
		SALESORDER_NO,SO_CUSTOMER_PO_NUMBER,SO_SHIPPING_METHOD,SO_SHIPPING_ACCOUNT,PART_NUMBER,C_NAME,
		( SELECT L_VENDOR_NAME FROM LOCATION WHERE LOCATION_ID = isnull( SHIPPING_LOCATION_ID, @PRM_DEFAULT_VENDOR )),
		SUBSTRING( ISNULL(SO_Comments, ''''), 0, 500 ),SUBSTRING( ISNULL(SO_Shipping_instructions, ''''), 0, 500 ),
		(SELECT LABEL FROM Customer_Shipping_Table CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT WHERE CST.CustomerShipping_ID=CSLIT.CustomerShipping_ID
		AND CST.SALESORDER_ID = SO.SALESORDER_ID AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT_STATUS = ''Issued'' )
	FROM
		    (((CUSTOMER_OPEN_ORDER_LINEITEM COOL 
			LEFT OUTER JOIN (SELECT SUM(CSLIT_QTY_SHIPPED) AS TOTAL,LINEITEM_ID 
				FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE 
				--WHERE CSLIT_STATUS !=''CANCELLED'' 
				WHERE CSLIT_STATUS =''SHIPPED'' 
				GROUP BY LINEITEM_ID) XYZ ON COOL.LINEITEM_ID = XYZ.LINEITEM_ID

		     INNER JOIN SALES_ORDER SO ON
		        COOL.SALESORDER_ID = SO.SALESORDER_ID )
		     INNER JOIN PRODUCT P ON
		        COOL.PRODUCT_ID = P.PRODUCT_ID)
		     INNER JOIN CUSTOMER C ON
		        SO.CUSTOMER_ID = C.CUSTOMER_ID)
		    LEFT OUTER JOIN SALES_ORDER_SHIPPING_LOCATIONS_TABLE SOSLT ON 
			COOL.LINEITEM_ID = SOSLT.LINEITEM_ID
	where 
		cool_status = ''approved'' AND ( ( SO_IS_CONSIGNMENT = 0 ) OR ( C_IS_Consignee = 1 and SO_IS_CONSIGNMENT = 1 ) ) AND (COOL_QTY-ISNULL(XYZ.TOTAL,0)) > 0 '
	

	IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SO.CUSTOMER_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @PRM_CUSTOMER_ID ))'
	END	
	IF ( @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0' and @PRODUCT_ID <> '-1' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND COOL.PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @PRM_PRODUCT_ID ))'  
	END
	IF ( @SO_NUMBER IS NOT NULL AND @SO_NUMBER <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SO.SALESORDER_NO = @PRM_SO_NUMBER '
	END

	IF ( @ORDER_TYPE IS NOT NULL AND @ORDER_TYPE <> -1 AND len(ltrim(rtrim(@ORDER_TYPE))) > 0 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SO.SO_Is_Sample = @PRM_ORDER_TYPE '
	END

	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( ISNULL(SHIPPING_LOCATION_ID,@PRM_DEFAULT_VENDOR ) IN ( SELECT LOCATION_ID FROM LOCATION WHERE VENDOR_ID = @PRM_VENDOR AND L_TYPE = ''FINISHED GOODS'') ) '
	END

	IF ( @DATERANGE IS NOT NULL AND @DATERANGE > 0 )
	BEGIN
		SET @START_DATE = CONVERT( DATETIME, CONVERT(VARCHAR,CURRENT_TIMESTAMP,101)+' 12:00AM' )
		SET @END_DATE =  CONVERT( DATETIME, CONVERT (VARCHAR, DATEADD(DD,@DATERANGE,CURRENT_TIMESTAMP), 101)+' 11:59PM' )
	END
	ELSE
	BEGIN
		SET @START_DATE = CONVERT( DATETIME, CONVERT(VARCHAR,CURRENT_TIMESTAMP,101)+' 12:00AM' )
		SET @END_DATE =  CONVERT( DATETIME, CONVERT (VARCHAR, DATEADD(DD,7,CURRENT_TIMESTAMP), 101)+' 11:59PM' )
	END
	--SET @SQLSTRING = @SQLSTRING + ' AND ISNULL(COOL_REVISED_DATE,COOL_SCHEDULE_DATE) BETWEEN @PRM_START_DATE AND @PRM_END_DATE '
	SET @SQLSTRING = @SQLSTRING + ' AND ISNULL(COOL_REVISED_DATE,COOL_SCHEDULE_DATE) < @PRM_END_DATE '
	PRINT @SQLSTRING
	EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_CUSTOMER_ID = @CUSTOMER_ID,@PRM_PRODUCT_ID = @PRODUCT_ID,@PRM_SO_NUMBER = @SO_NUMBER,@PRM_ORDER_TYPE = @ORDER_TYPE,@PRM_START_DATE = @START_DATE,@PRM_END_DATE = @END_DATE,@PRM_VENDOR = @VENDOR,@PRM_DEFAULT_VENDOR = @DEFAULT_VENDOR
	
	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		SELECT 
			[SALES ORDER NUMBER],[LINE ITEM NO],CUSTOMER,[CUSTOMER PO NUMBER],[PACKING SLIP NO],
			[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QTR],
			[SCHEDULE DATE],[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER],
			[PART NUMBER],[COOL QTY],[SHIPPING METHOD],VENDOR,[SHIPPING ACCOUNT], [COMMENTS], [Shipping Instructions]
		FROM 
			#PENDING_SHIPMENT_TABLE ORDER BY [SCHEDULE DATE]
	END
	ELSE
	BEGIN
		SELECT 
			 [LINE ITEM NO],[COOL QTY],[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],
			 [REQUESTED DATE QTR],[SCHEDULE DATE],[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER],
			 [SALES ORDER NUMBER],[CUSTOMER PO NUMBER],[SHIPPING METHOD],
			 [SHIPPING ACCOUNT],[PART NUMBER],CUSTOMER,VENDOR
		FROM 
			#PENDING_SHIPMENT_TABLE ORDER BY [SCHEDULE DATE]
	END
END
